from flask import Flask
from flask import render_template, request
import pymysql
import pandas as pd
from datetime import datetime


app = Flask(__name__)


@app.route('/')
def index():
    product_list = get_product_list()
    return render_template('index.html', product_list=product_list, ts='')


def get_product_list():
    item1 = {
        'id': 1,
        'name': '力得垃圾袋',
        'price': 24.9,
        'img': 'https://img11.360buyimg.com/n7/jfs/t1/184522/20/18466/308994/61133fa3E185a74a1/b927d2ddf54be5df.jpg',
    }

    item2 = {
        'id': 2,
        'name': '维达卷纸',
        'price': 31.5,
        'img': 'https://img10.360buyimg.com/n7/jfs/t1/214850/31/8612/260369/61c2de7fEd4370647/65a3239a21ab0955.jpg',
    }

    item3 = {
        'id': 3,
        'name': '泡泡机',
        'price': 39.0,
        'img': 'https://img14.360buyimg.com/n7/jfs/t1/202221/12/4744/510774/6133308fE73e26ffd/392a55b1234d91c5.jpg',
    }

    return [item1, item2, item3]


@app.route('/buy', methods=['post'])
def buy():
    r = request.form
    product_list = get_product_list()
    total = 0
    for product in product_list:
        total += int(r.get(str(product['id'])))*product['price']
    try:
        money = float(r.get('money'))
    except:
        money = 0
    if total == 0:
        ts = '请选择商品！'
    elif total > money:
        ts = '余额不足！'
    else:
        ts = '购买成功！'
    return render_template('index.html', product_list=product_list, ts=ts)


@app.route('/course')
def kebiao():
    courses = get_all_course()
    return render_template('course.html', ts='', courses=courses)


@app.route('/get15', methods=["POST"])
def get15():
    courses = get_all_course()
    df = pd.DataFrame(courses)

    df['now'] = datetime.now()
    df['now'] = pd.to_datetime(df['now'])
    df['course_time'] = pd.to_datetime(df['course_time'])
    df = df[df['now'] < df['course_time']]
    df = df.sort_values('course_time')
    df = df.reset_index(drop=True)

    if len(df) > 0:
        info = df.loc[0]
        delay = info['course_time']-datetime.now()
        print(int(delay.seconds) < 15*60)
        ts = ''
        if int(delay.seconds) < 15*60:
            ts = '快上课啦！\n地点:{}\n时间:{}\n课程:{}'.format(
                info['classroom'], info['course_time'], info['course_name'])
            return {'ts':ts}
        return {'ts':'wait'}
    return {'ts':'放假了！'}


@app.route('/del', methods=['get'])
def getdel():
    id = request.args.get('id')
    del_course(id)
    ts = '删除成功！'
    return render_template('course.html', ts=ts)


@app.route('/add', methods=['post'])
def getadd():
    r = request.form
    date_ = r.get('1.1')
    time_ = r.get('1.2')
    course_num = r.get('2')
    classroom = r.get('3')
    course_name = r.get('4')
    course_time = date_+' '+time_

    course = [course_time, course_num, classroom, course_name]
    try:
        add_course(course)
        ts = '添加成功！'
    except:
        ts = '添加失败！'

    return render_template('course.html', ts=ts)


def get_all_course():
    con, cur = opendb()
    cur.execute(
        'SELECT id, course_time, course_num, classroom, course_name FROM course')
    result = cur.fetchall()
    column = [index[0] for index in cur.description]
    data_dict = [dict(zip(column, row)) for row in result]
    cur.close()
    con.close()

    return data_dict


def add_course(data):
    con, cur = opendb()
    add_sql = "INSERT INTO course (course_time, course_num, classroom, course_name) VALUES ('{}',{},'{}','{}')".format(
        data[0], data[1], data[2], data[3])
    cur.execute(add_sql)
    con.commit()
    cur.close()
    con.close()


def del_course(id):
    con, cur = opendb()
    del_sql = "delete from course where id={}".format(id)
    cur.execute(del_sql)
    con.commit()
    cur.close()
    con.close()


def opendb():
    con = pymysql.connect(host='127.0.0.1',
                          user='root2',
                          password='123456',
                          database='root2')
    cur = con.cursor()
    # sss='''
    #     CREATE TABLE `course` (
    #       `id` int(11) NOT NULL AUTO_INCREMENT,
    #       `course_time` datetime(6) DEFAULT NULL,
    #       `course_num` int(11) DEFAULT NULL,
    #       `classroom` varchar(100) DEFAULT NULL,
    #       `course_name` varchar(100) DEFAULT NULL,
    #       PRIMARY KEY (`id`)
    #     ) ENGINE=MyISAM DEFAULT CHARSET=utf8'''
    # cur.execute(sss)
    # con.commit()
    return con, cur


if __name__ == "__main__":
    app.run()
